USE SAS
GO

 IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'GetAccountTasks')
	BEGIN
		DROP  Procedure  dbo.GetAccountTasks
	END

GO

CREATE Procedure dbo.GetAccountTasks
	@accountGUID	VARCHAR(100)
,	@userId			VARCHAR(10)
AS
BEGIN

	;WITH A AS (
		SELECT 
			a.ActivityTaskID	AS TaskID
		,	a.ActivityTaskGUID	AS PKID
		,	a.[Description]		AS TaskName
		,	s.StatusDesc		as ActivityStatus
		,	s.SortOrder			as StatusOrder
		,	a.StartDate			as StartDate
		,	a.EndDate			as EndDate
		FROM TR_ACTIVITYTASK as a WITH (NOLOCK)
		INNER JOIN TR_RELATIONSHIPS as r WITH (NOLOCK)
			on r.SourceGUID = a.ActivityTaskGUID
		LEFT OUTER JOIN TR_STATUS as s WITH (NOLOCK)
			on s.Status = a.Status
		WHERE 
			r.Partner2 = @userID
		AND r.RelationShipType = 3 -- OWNER
		AND	ISNULL(a.IsTask,0) = 1
	), B AS (
		-- GET ACCOUNTS FOR THE TASKS
		SELECT 
			act.PartnerName		as 	AccountName
		,	a.TaskID
		FROM A 
		INNER JOIN TR_RELATIONSHIPS as r WITH (NOLOCK)
			ON r.SourceGUID = a.PKID
		INNER JOIN TR_PARTNER as act WITH (NOLOCK)
			on act.PartnerNo = r.Partner2
		WHERE
			r.RelationShiptype = 10 -- 10: Task Partner
		AND act.PartnerGUID = @accountGUID
	), Final AS (
		SELECT
			a.TaskName			AS [Task Name]
		,	a.ActivityStatus	as [Status]
		,	CONVERT(VARCHAR(10), a.EndDate, 101)			as [Due Date]
		,	a.PKID				AS [PKID]
		,	a.StatusOrder
		FROM A
		INNER JOIN B
			on B.TaskID = A.TaskID
	)

	SELECT 
		[Task Name]
	,	[Status]
	,	[Due Date]
	,	[PKID]
	FROM Final 
	ORDER BY StatusOrder, [Due Date], [Task Name]

END
GO

-- exec GetAccountTasks 1